import time
import pymysql
import psycopg2
import sqlite3
from django.db import connections
from django.conf import settings
from .models import DatabaseConnection, DatabaseMonitoring, DatabaseAlert


class DatabaseConnectionManager:
    """数据库连接管理器"""
    
    @staticmethod
    def create_connection(db_config):
        """创建数据库连接"""
        engine = db_config.get('engine', 'mysql')
        
        try:
            if engine == 'mysql':
                connection = pymysql.connect(
                    host=db_config['host'],
                    port=db_config['port'],
                    user=db_config['username'],
                    password=db_config['password'],
                    database=db_config['database'],
                    charset='utf8mb4'
                )
            elif engine == 'postgresql':
                connection = psycopg2.connect(
                    host=db_config['host'],
                    port=db_config['port'],
                    user=db_config['username'],
                    password=db_config['password'],
                    database=db_config['database']
                )
            elif engine == 'sqlite3':
                connection = sqlite3.connect(db_config['database'])
            else:
                raise ValueError(f"不支持的数据库类型: {engine}")
            
            return connection
            
        except Exception as e:
            raise Exception(f"数据库连接失败: {str(e)}")
    
    @staticmethod
    def test_connection(db_conn):
        """测试数据库连接"""
        db_config = {
            'engine': db_conn.engine,
            'host': db_conn.host,
            'port': db_conn.port,
            'username': db_conn.username,
            'password': db_conn.password,
            'database': db_conn.database
        }
        
        start_time = time.time()
        
        try:
            connection = DatabaseConnectionManager.create_connection(db_config)
            
            # 执行简单查询测试
            cursor = connection.cursor()
            if db_conn.engine == 'mysql':
                cursor.execute("SELECT 1")
            elif db_conn.engine == 'postgresql':
                cursor.execute("SELECT 1")
            elif db_conn.engine == 'sqlite3':
                cursor.execute("SELECT 1")
            
            result = cursor.fetchone()
            cursor.close()
            connection.close()
            
            response_time = (time.time() - start_time) * 1000  # 转换为毫秒
            
            return {
                'success': True,
                'response_time': response_time,
                'message': '连接测试成功'
            }
            
        except Exception as e:
            response_time = (time.time() - start_time) * 1000
            
            # 创建连接失败告警
            DatabaseAlert.objects.create(
                connection=db_conn,
                alert_type='connection_failed',
                severity='high',
                message=f'数据库连接失败: {str(e)}',
                current_value=response_time
            )
            
            return {
                'success': False,
                'response_time': response_time,
                'message': f'连接测试失败: {str(e)}'
            }
    
    @staticmethod
    def get_database_info(db_conn):
        """获取数据库信息"""
        db_config = {
            'engine': db_conn.engine,
            'host': db_conn.host,
            'port': db_conn.port,
            'username': db_conn.username,
            'password': db_conn.password,
            'database': db_conn.database
        }
        
        try:
            connection = DatabaseConnectionManager.create_connection(db_config)
            cursor = connection.cursor()
            
            info = {}
            
            if db_conn.engine == 'mysql':
                # 获取MySQL版本
                cursor.execute("SELECT VERSION()")
                info['version'] = cursor.fetchone()[0]
                
                # 获取表数量
                cursor.execute("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %s", (db_conn.database,))
                info['table_count'] = cursor.fetchone()[0]
                
                # 获取数据库大小
                cursor.execute("""
                    SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'DB Size in MB'
                    FROM information_schema.tables 
                    WHERE table_schema = %s
                """, (db_conn.database,))
                result = cursor.fetchone()
                info['database_size'] = result[0] if result[0] else 0
                
                # 获取连接数
                cursor.execute("SHOW STATUS LIKE 'Threads_connected'")
                info['connection_count'] = int(cursor.fetchone()[1])
                
            elif db_conn.engine == 'postgresql':
                # 获取PostgreSQL版本
                cursor.execute("SELECT version()")
                info['version'] = cursor.fetchone()[0]
                
                # 获取表数量
                cursor.execute("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public'")
                info['table_count'] = cursor.fetchone()[0]
                
                # 获取数据库大小
                cursor.execute("SELECT pg_size_pretty(pg_database_size(%s))", (db_conn.database,))
                info['database_size'] = cursor.fetchone()[0]
                
                # 获取连接数
                cursor.execute("SELECT count(*) FROM pg_stat_activity")
                info['connection_count'] = cursor.fetchone()[0]
                
            elif db_conn.engine == 'sqlite3':
                # 获取SQLite版本
                cursor.execute("SELECT sqlite_version()")
                info['version'] = cursor.fetchone()[0]
                
                # 获取表数量
                cursor.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table'")
                info['table_count'] = cursor.fetchone()[0]
                
                # 获取数据库大小（文件大小）
                import os
                if os.path.exists(db_conn.database):
                    info['database_size'] = round(os.path.getsize(db_conn.database) / 1024 / 1024, 2)
                else:
                    info['database_size'] = 0
                
                info['connection_count'] = 1  # SQLite是文件数据库
            
            cursor.close()
            connection.close()
            
            return info
            
        except Exception as e:
            raise Exception(f"获取数据库信息失败: {str(e)}")
    
    @staticmethod
    def get_table_list(db_conn):
        """获取数据库表列表"""
        db_config = {
            'engine': db_conn.engine,
            'host': db_conn.host,
            'port': db_conn.port,
            'username': db_conn.username,
            'password': db_conn.password,
            'database': db_conn.database
        }
        
        try:
            connection = DatabaseConnectionManager.create_connection(db_config)
            cursor = connection.cursor()
            
            tables = []
            
            if db_conn.engine == 'mysql':
                cursor.execute("SHOW TABLES")
                tables = [row[0] for row in cursor.fetchall()]
                
            elif db_conn.engine == 'postgresql':
                cursor.execute("SELECT tablename FROM pg_tables WHERE schemaname = 'public'")
                tables = [row[0] for row in cursor.fetchall()]
                
            elif db_conn.engine == 'sqlite3':
                cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
                tables = [row[0] for row in cursor.fetchall()]
            
            cursor.close()
            connection.close()
            
            return tables
            
        except Exception as e:
            raise Exception(f"获取表列表失败: {str(e)}")
    
    @staticmethod
    def execute_query(db_conn, query):
        """执行SQL查询"""
        db_config = {
            'engine': db_conn.engine,
            'host': db_conn.host,
            'port': db_conn.port,
            'username': db_conn.username,
            'password': db_conn.password,
            'database': db_conn.database
        }
        
        start_time = time.time()
        
        try:
            connection = DatabaseConnectionManager.create_connection(db_config)
            cursor = connection.cursor()
            
            cursor.execute(query)
            
            # 获取查询结果
            if query.strip().upper().startswith('SELECT'):
                columns = [desc[0] for desc in cursor.description]
                rows = cursor.fetchall()
                result = {
                    'columns': columns,
                    'rows': [list(row) for row in rows],
                    'row_count': len(rows)
                }
            else:
                connection.commit()
                result = {
                    'rows_affected': cursor.rowcount
                }
            
            execution_time = (time.time() - start_time) * 1000
            
            cursor.close()
            connection.close()
            
            return {
                'success': True,
                'data': result,
                'execution_time': execution_time
            }
            
        except Exception as e:
            execution_time = (time.time() - start_time) * 1000
            
            return {
                'success': False,
                'error': str(e),
                'execution_time': execution_time
            }
